<?php
class Lead extends CI_Model{

	/* table name */
	private $tbl_name = 'leads';

	/* columns name */
	private $col_offerId = 'offerId';
	private $col_networkId = 'networkId';
	private $col_username = 'username';
	private $col_point = 'point';
	private $col_time = 'time';

	/* contructor */
	public function __contruct(){

		parent::__construct();
	}

	public function add( $offerId, $networkId, $username, $point ){

		$data = array(
		   $this->col_offerId 		=> $offerId,
		   $this->col_networkId 	=> $networkId,
		   $this->col_username 		=> $username,
		   $this->col_point 		=> $point,
		   $this->col_time			=> date('Y-m-d H:i:s')
		);

		return $this->db->insert($this->tbl_name, $data);
	}


	public function get_new_leads($oldLeadId=0){

		$queryString = 
		"SELECT tbl_leads_user.leadId, tbl_leads_user.offerId, tbl_leads_user.point, tbl_leads_user.time,
			   tbl_members.memberName,
			   tbl_networks.networkName	
		FROM
					(SELECT tbl_leads.leadId, tbl_leads.offerId, tbl_leads.networkId, tbl_leads.point, tbl_leads.time, tbl_user.ref
					FROM
						(SELECT * FROM leads WHERE leads.leadId > $oldLeadId) AS tbl_leads
						JOIN
						(SELECT username, ref FROM user) AS tbl_user
						ON tbl_leads.username = tbl_user.username
					) AS tbl_leads_user

					join
					(SELECT memberId, memberName FROM members) AS tbl_members
					on tbl_leads_user.ref = tbl_members.memberId

			
					join
					(SELECT networkId, networkName FROM networks) AS tbl_networks
					on tbl_leads_user.networkId = tbl_networks.networkId
		ORDER BY leadId DESC";

		$query = $this->db->query($queryString);
		
		return $query->result();		
	}

	public function get_leads(){

		$queryString = 
		"SELECT tbl_leads_user.leadId, tbl_leads_user.offerId, tbl_leads_user.point, tbl_leads_user.time,
			   tbl_members.memberName,
			   tbl_networks.networkName	
		FROM
					(SELECT tbl_leads.leadId, tbl_leads.offerId, tbl_leads.networkId, tbl_leads.point, tbl_leads.time, tbl_user.ref
					FROM
						(SELECT * FROM leads WHERE leads.time > DATE_SUB(NOW(), INTERVAL 12 HOUR)) AS tbl_leads
						JOIN
						(SELECT username, ref FROM user) AS tbl_user
						ON tbl_leads.username = tbl_user.username
					) AS tbl_leads_user

					join
					(SELECT memberId, memberName FROM members) AS tbl_members
					on tbl_leads_user.ref = tbl_members.memberId

			
					join
					(SELECT networkId, networkName FROM networks) AS tbl_networks
					on tbl_leads_user.networkId = tbl_networks.networkId
		ORDER BY leadId DESC";

		$query = $this->db->query($queryString);
		
		return $query->result();		
	}
	
}
?>